/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Book;

import BookCategory.BookCategoryBean;
import BookType.BookTypeBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int addBook(BookBean bb) throws SQLException {
       int i=0;
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("insert into book values(bookid.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate)");
           pre.setInt(1, bb.getBookCategoryId());
           pre.setInt(2, bb.getBookTypeId());
           pre.setString(3, bb.getBookName());
           pre.setString(4, bb.getPublisher());
           pre.setString(5, bb.getAuthor());
           pre.setString(6, bb.getYear());
           pre.setInt(7, bb.getQuantity());
           pre.setFloat(8, bb.getPrice());
           pre.setInt(9, bb.getFine());
           pre.setInt(10, bb.getGracePeriod());
           pre.setString(11, bb.getEntryEmail());
           pre.setString(12, bb.getIsbn());
           pre.setString(13, bb.getBookDescription());
           i=pre.executeUpdate();
           if(i!=0)
           {
               
               PreparedStatement pre1=con.prepareStatement("insert into book_details values(bookdetailid.nextval,bookid.currval,?||'-'||?||'-'||bookid.currval||'-'||bookdetailid.currval,0,'',sysdate)");
               for(int j=1; j<=bb.getQuantity(); j++)
               {
               pre1.setInt(1, bb.getBookCategoryId());
               pre1.setInt(2, bb.getBookTypeId());
               pre1.addBatch();
               }
               int l[]=pre1.executeBatch();
               if(l.length>0){
               i=1;
               }
           }
       }catch(Exception e)
       {
           System.out.println(e);
       }finally{
           con.close();
       }
       return i;
    }
    
public static int getMaxId() throws SQLException
{
    int i=0;
    try
    {
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select max(book_id) from book");
        ResultSet rs=pre.executeQuery();
        if(rs.next())
        {
            i=rs.getInt(1);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return i;
}
public static BookBean viewSpecific(String cname,int val) throws SQLException
{
    BookBean bb=new BookBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from BOOK b left join book_type bt on b.book_type_id=bt.book_type_id left join book_category bc on bc.book_category_id= bt.book_category_id left join book_category bcc on b.BOOK_CATEGORY_ID= bcc.BOOK_CATEGORY_ID where "+cname+"="+val+"");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            BookCategoryBean bcb=new BookCategoryBean();
            BookTypeBean btb=new BookTypeBean();
            bb.setBookId(rs.getInt(1));
            bb.setBookCategoryId(rs.getInt(2));
            bb.setBookTypeId(rs.getInt(3));
            bb.setBookName(rs.getString(4));
            bb.setPublisher(rs.getString(5));
            bb.setAuthor(rs.getString(6));
            bb.setYear(rs.getString(7));
            bb.setQuantity(rs.getInt(8));
            bb.setPrice(rs.getInt(9));
            bb.setFine(rs.getInt(10));
            bb.setGracePeriod(rs.getInt(11));
            bb.setEntryEmail(rs.getString(12));
            bb.setIsbn(rs.getString(13));
            bb.setBookDescription(rs.getString(14));
            bb.setEntryDate(rs.getDate(15));
            
            btb.setBookTypeId(rs.getInt(16));
            btb.setBookCategoryId(rs.getInt(17));
            btb.setBookType(rs.getString(18));
            btb.setEntryDate(rs.getDate(19));
            bcb.setBookCategoryId(rs.getInt(20));
            bcb.setBookCategory(rs.getString(21));
            bcb.setEntryDate(rs.getDate(22));
            btb.setBookCategoryBean(bcb);
            bb.setBookTypeBean(btb);
            
            bcb.setBookCategoryId(rs.getInt(23));
            bcb.setBookCategory(rs.getString(24));
            bcb.setEntryDate(rs.getDate(25));
            bb.setBookCategoryBean(bcb);
        }
    }catch(Exception e)
    {
        System.out.println(e);
    }finally
    {
        con.close();
    }return bb;
    
}
public static Vector viewAll() throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from BOOK b left join book_type bt on b.book_type_id=bt.book_type_id left join book_category bc on bc.book_category_id= bt.book_category_id left join book_category bcc on b.BOOK_CATEGORY_ID= bcc.BOOK_CATEGORY_ID ");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            BookBean bb=new BookBean();
            BookCategoryBean bcb=new BookCategoryBean();
            BookTypeBean btb=new BookTypeBean();
            bb.setBookId(rs.getInt(1));
            bb.setBookCategoryId(rs.getInt(2));
            bb.setBookTypeId(rs.getInt(3));
            bb.setBookName(rs.getString(4));
            bb.setPublisher(rs.getString(5));
            bb.setAuthor(rs.getString(6));
            bb.setYear(rs.getString(7));
            bb.setQuantity(rs.getInt(8));
            bb.setPrice(rs.getInt(9));
            bb.setFine(rs.getInt(10));
            bb.setGracePeriod(rs.getInt(11));
            bb.setEntryEmail(rs.getString(12));
            bb.setIsbn(rs.getString(13));
            bb.setBookDescription(rs.getString(14));
            bb.setEntryDate(rs.getDate(15));
            
            btb.setBookTypeId(rs.getInt(16));
            btb.setBookCategoryId(rs.getInt(17));
            btb.setBookType(rs.getString(18));
            btb.setEntryDate(rs.getDate(19));
            bcb.setBookCategoryId(rs.getInt(20));
            bcb.setBookCategory(rs.getString(21));
            bcb.setEntryDate(rs.getDate(22));
            btb.setBookCategoryBean(bcb);
            bb.setBookTypeBean(btb);
            
            bcb.setBookCategoryId(rs.getInt(23));
            bcb.setBookCategory(rs.getString(24));
            bcb.setEntryDate(rs.getDate(25));
            bb.setBookCategoryBean(bcb);
            
            list.add(bb);
        }
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{con.close();}
    return list;
}
public static int addBookDetails(BookDetailsBean bdb) throws SQLException {
       int i=0, k=0;
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("insert into book_details values(bookdetailsid.nextval,?,?,?,?,sysdate)");
           pre.setInt(1, bdb.getBookId());
           pre.setString(2, bdb.getBookNumber());
           pre.setInt(3, bdb.getBookStatus());
           pre.setString(4, bdb.getBookStatusDescription());
           i=pre.executeUpdate();
           if(i!=0)
           {
               k=getMaxIdDetails();
           }
       }catch(Exception e)
       {
           System.out.println(e);
       }finally{
           con.close();
       }
       return k;
    }
public static int getMaxIdDetails() throws SQLException
{
    int i=0;
    try
    {
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select max(book_details_id) from book_details");
        ResultSet rs=pre.executeQuery();
        if(rs.next())
        {
            i=rs.getInt(1);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return i;
}
public static BookDetailsBean viewSpecificDetails(String cname,String val) throws SQLException
{
    BookDetailsBean bdb=new BookDetailsBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from book_details left join book on book_details.book_id=book.book_id left join book_category on BOOK.BOOK_CATEGORY_ID= BOOK_CATEGORY.BOOK_CATEGORY_ID left join book_type on book.book_type_id=book_type.book_type_id where "+cname+"="+val+"");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            BookBean bb=new BookBean();
            BookCategoryBean bcb=new BookCategoryBean();
            BookTypeBean btb=new BookTypeBean();
            bdb.setBookDetailsId(rs.getInt(1));
            bdb.setBookId(rs.getInt(2));
            bdb.setBookNumber(rs.getString(3));
            bdb.setBookStatus(rs.getInt(4));
            bdb.setBookStatusDescription(rs.getString(5));
            bdb.setEntryDate(rs.getDate(6));
            bb.setBookId(rs.getInt(7));
            bb.setBookCategoryId(rs.getInt(7));
            bb.setBookTypeId(rs.getInt(8));
            bb.setBookName(rs.getString(9));
            bb.setPublisher(rs.getString(10));
            bb.setAuthor(rs.getString(11));
            bb.setYear(rs.getString(12));
            bb.setQuantity(rs.getInt(13));
            bb.setPrice(rs.getInt(14));
            bb.setFine(rs.getInt(15));
            bb.setGracePeriod(rs.getInt(16));
            bb.setEntryEmail(rs.getString(17));
            bb.setIsbn(rs.getString(18));
            bb.setBookDescription(rs.getString(19));
            bb.setEntryDate(rs.getDate(20));
            bcb.setBookCategoryId(rs.getInt(21));
            bcb.setBookCategory(rs.getString(22));
            bcb.setEntryDate(rs.getDate(23));
            btb.setBookTypeId(rs.getInt(24));
            btb.setBookCategoryId(rs.getInt(25));
            btb.setBookType(rs.getString(36));
            bb.setBookCategoryBean(bcb);
            bb.setBookTypeBean(btb);
            bdb.setBookBean(bb);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }
    return bdb;
        
}
public static Vector viewAllDetails() throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from book_details left join book on book_details.book_id=book.book_id left join book_category on BOOK.BOOK_CATEGORY_ID= BOOK_CATEGORY.BOOK_CATEGORY_ID left join book_type on book.book_type_id=book_type.book_type_id");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            BookDetailsBean bdb=new BookDetailsBean();
            BookBean bb=new BookBean();
            BookCategoryBean bcb=new BookCategoryBean();
            BookTypeBean btb=new BookTypeBean();
            bdb.setBookDetailsId(rs.getInt(1));
            bdb.setBookId(rs.getInt(2));
            bdb.setBookNumber(rs.getString(3));
            bdb.setBookStatus(rs.getInt(4));
            bdb.setBookStatusDescription(rs.getString(5));
            bdb.setEntryDate(rs.getDate(6));
            bb.setBookId(rs.getInt(7));
            bb.setBookCategoryId(rs.getInt(7));
            bb.setBookTypeId(rs.getInt(8));
            bb.setBookName(rs.getString(9));
            bb.setPublisher(rs.getString(10));
            bb.setAuthor(rs.getString(11));
            bb.setYear(rs.getString(12));
            bb.setQuantity(rs.getInt(13));
            bb.setPrice(rs.getInt(14));
            bb.setFine(rs.getInt(15));
            bb.setGracePeriod(rs.getInt(16));
            bb.setEntryEmail(rs.getString(17));
            bb.setIsbn(rs.getString(18));
            bb.setBookDescription(rs.getString(19));
            bb.setEntryDate(rs.getDate(20));
            bcb.setBookCategoryId(rs.getInt(21));
            bcb.setBookCategory(rs.getString(22));
            bcb.setEntryDate(rs.getDate(23));
            btb.setBookTypeId(rs.getInt(24));
            btb.setBookCategoryId(rs.getInt(25));
            btb.setBookType(rs.getString(36));
            bb.setBookCategoryBean(bcb);
            bb.setBookTypeBean(btb);
            bdb.setBookBean(bb);
            list.add(bdb);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }
    return list;
        
}
public static int edit(BookBean bb) throws SQLException{
  int i=0;
  try{
      con=DBConnection.Connection.getConnection();
      PreparedStatement pre=con.prepareStatement("update book set BOOK_CATEGORY_ID=?, BOOK_TYPE_ID=?, BOOK_NAME=?, PUBLISHER=?, AUTHOR=?, YEAR=?, QUANTITY=?, PRICE=?, FINE=?, GRACE_PERIOD=?, ENTRY_EMAIL=?, ISBN=?, BOOK_DESCRIPTION=?, ENTRY_DATE=sysdate where BOOK_ID=?");
      pre.setInt(1, bb.getBookCategoryId());
      pre.setInt(2, bb.getBookTypeId());
      pre.setString(3, bb.getBookName());
      pre.setString(4, bb.getPublisher());
      pre.setString(5, bb.getAuthor());
      pre.setString(6, bb.getYear());
      pre.setInt(7, bb.getQuantity());
      pre.setFloat(8, bb.getPrice());
      pre.setInt(9, bb.getFine());
      pre.setInt(10, bb.getGracePeriod());
      pre.setString(11, bb.getEntryEmail());
      pre.setString(12, bb.getIsbn());
      pre.setString(13, bb.getBookDescription());
      pre.setInt(14, bb.getBookId());
      i=pre.executeUpdate();
  }catch(Exception e){
      System.out.println(e);
  }finally{
      con.close();
  }return i;
}
public static int delete(int id) throws SQLException{
    int i=0,k=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre1=con.prepareStatement("delete from BOOK_DETAILS where book_id=?");
        pre1.setInt(1, id);
        k=pre1.executeUpdate();
        if(k!=0){
        PreparedStatement pre=con.prepareStatement("delete from book where book_id=?");
        pre.setInt(1, id);
        i=pre.executeUpdate();
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{con.close();}
    return i;
}
public static String viewbookname(String bid) throws SQLException{
    String bname=null;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select book.book_name from book_details left join book on book_details.book_id=book.book_id where book_details.book_number=? and book_details.book_status=0");
        pre.setString(1, bid);
        ResultSet rs=pre.executeQuery();
        if(rs.next()){
            
            bname=rs.getString(1);
        }
    }catch(Exception e){
        System.out.println(e);
    }
    finally{
        con.close();
    }return bname;
}
public static int getGracePeriod(int id) throws SQLException{
    int i=0;
    try{
   con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select GRACE_PERIOD from BOOK where BOOK_ID=?");
        pre.setInt(1, id);
        ResultSet rs=pre.executeQuery();
        if(rs.next()){
            i=rs.getInt(1);
        }
    }catch(Exception e){
        System.out.println(e);
    }
    finally{
        con.close();
    }return i;
}
public static BookBean viewBook(int bookid)throws SQLException{
    
    BookBean bb=new BookBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select BOOK_NAME, FINE, price from book where book_id=?");
        pre.setInt(1, bookid);
        ResultSet rs=pre.executeQuery();
        if(rs.next()){
          bb.setBookName(rs.getString(1));
          bb.setFine(rs.getInt(2));
          bb.setPrice(rs.getInt(3));
        }
        
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return bb;
}
public static Vector searchTotalRecord(String val) throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre;
        pre = con.prepareStatement("select * from BOOK b left join book_type bt on b.book_type_id=bt.book_type_id left join book_category bc on bc.book_category_id= bt.book_category_id left join book_category bcc on b.BOOK_CATEGORY_ID= bcc.BOOK_CATEGORY_ID where upper(b.book_name) like upper('"+val+"%') or lower(b.publisher) like lower('"+val+"%') or upper(b.author) like upper('"+val+"%') or lower(bt.book_type) like lower('"+val+"%') or upper(bc.book_category) like upper('"+val+"%')");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            BookBean bb=new BookBean();
            BookCategoryBean bcb=new BookCategoryBean();
            BookTypeBean btb=new BookTypeBean();
            bb.setBookId(rs.getInt(1));
            bb.setBookCategoryId(rs.getInt(2));
            bb.setBookTypeId(rs.getInt(3));
            bb.setBookName(rs.getString(4));
            bb.setPublisher(rs.getString(5));
            bb.setAuthor(rs.getString(6));
            bb.setYear(rs.getString(7));
            bb.setQuantity(rs.getInt(8));
            bb.setPrice(rs.getInt(9));
            bb.setFine(rs.getInt(10));
            bb.setGracePeriod(rs.getInt(11));
            bb.setEntryEmail(rs.getString(12));
            bb.setIsbn(rs.getString(13));
            bb.setBookDescription(rs.getString(14));
            bb.setEntryDate(rs.getDate(15));
            
            btb.setBookTypeId(rs.getInt(16));
            btb.setBookCategoryId(rs.getInt(17));
            btb.setBookType(rs.getString(18));
            btb.setEntryDate(rs.getDate(19));
            bcb.setBookCategoryId(rs.getInt(20));
            bcb.setBookCategory(rs.getString(21));
            bcb.setEntryDate(rs.getDate(22));
            btb.setBookCategoryBean(bcb);
            bb.setBookTypeBean(btb);
            
            bcb.setBookCategoryId(rs.getInt(23));
            bcb.setBookCategory(rs.getString(24));
            bcb.setEntryDate(rs.getDate(25));
            bb.setBookCategoryBean(bcb);
            
            list.add(bb);
        }
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{con.close();}
    return list;
}

public static Vector viewSearch(String bname, int bcat) throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from BOOK b left join book_type bt on b.book_type_id=bt.book_type_id left join book_category bc on bc.book_category_id= bt.book_category_id left join book_category bcc on b.BOOK_CATEGORY_ID= bcc.BOOK_CATEGORY_ID where bc.book_category_id=?  and book_name like '?%'");
        pre.setInt(1, bcat);
        pre.setString(2, bname);
        
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            BookBean bb=new BookBean();
            BookCategoryBean bcb=new BookCategoryBean();
            BookTypeBean btb=new BookTypeBean();
            bb.setBookId(rs.getInt(1));
            bb.setBookCategoryId(rs.getInt(2));
            bb.setBookTypeId(rs.getInt(3));
            bb.setBookName(rs.getString(4));
            bb.setPublisher(rs.getString(5));
            bb.setAuthor(rs.getString(6));
            bb.setYear(rs.getString(7));
            bb.setQuantity(rs.getInt(8));
            bb.setPrice(rs.getInt(9));
            bb.setFine(rs.getInt(10));
            bb.setGracePeriod(rs.getInt(11));
            bb.setEntryEmail(rs.getString(12));
            bb.setIsbn(rs.getString(13));
            bb.setBookDescription(rs.getString(14));
            bb.setEntryDate(rs.getDate(15));
            
            btb.setBookTypeId(rs.getInt(16));
            btb.setBookCategoryId(rs.getInt(17));
            btb.setBookType(rs.getString(18));
            btb.setEntryDate(rs.getDate(19));
            bcb.setBookCategoryId(rs.getInt(20));
            bcb.setBookCategory(rs.getString(21));
            bcb.setEntryDate(rs.getDate(22));
            btb.setBookCategoryBean(bcb);
            bb.setBookTypeBean(btb);
            
            bcb.setBookCategoryId(rs.getInt(23));
            bcb.setBookCategory(rs.getString(24));
            bcb.setEntryDate(rs.getDate(25));
            bb.setBookCategoryBean(bcb);
            
            list.add(bb);
        }
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{con.close();}
    return list;
}
}
